package org.qloudgen.borg.utils.export;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ExcelFile {

	private HSSFWorkbook book = new HSSFWorkbook();
	private List<String> headers;
	private String sheetTitle;
	private String fileName;

	/**
	 * @return the headers
	 */
	public List<String> getHeaders() {
		return headers;
	}

	/**
	 * @param headers the headers to set
	 */
	public void setHeaders(List<String> headers) {
		this.headers = headers;
	}

	public ExcelFile(String fileName, String sheetTitle){
		this.fileName = fileName;
		this.sheetTitle = sheetTitle;
	}
	
	public void createSheet(String title){
		
		HSSFSheet sheet = book.createSheet(title);
		
		if (headers == null) return;
		
		// set header line on sheet
		HSSFRow row = sheet.createRow(0);  
        for ( int i = 0; i < headers.size(); i++)  
           row.createCell(i).setCellValue( new HSSFRichTextString(headers.get(i)));  
        
	}
	
	public void writeDataToExcel(ResultSet res, int limit) throws SQLException{
		
		ResultSetMetaData meta= res.getMetaData();
		int i = 0,j = 0;
		this.createSheet(this.sheetTitle + "-" + j);
		
		while(res.next()){
			
			 i++;
			 HSSFRow row = book.getSheetAt(j).createRow(i); 
			 for(int k = 0; k< meta.getColumnCount(); k++)  
				 row.createCell(k).setCellValue(res.getString(k+1));				 
			 
			 if (i >= limit) {
				 j++;
				 i = 0;
				 this.createSheet(this.sheetTitle + "-" + j);
			 }
		}
	}
	
	public void saveToFile() throws Exception{
		
		if (this.fileName == null) return;
		
		OutputStream out = new FileOutputStream(new File(this.fileName));
		
		book.write(out);  
		
		out.close();
	}
}
